/* Program name: 07_get0708locvars.sas;
/* Objective: pull the TU zipcode, state, county, etc variables for 97-01 from the 07 dataset.*/

libname in '/data';
libname score07 '/data/';
libname score08 '/data/';

/* pull the variables from the 2007 SCORE dataset */
data score07;
     set score07.tu2007_june (keep= permid CBSA: CensusBlockGroup: CensusSuffix: CensusTract: CountyFIPSCode: Latitude: Longitude: StateFIPSCode: zipcode);
run;

data score08;
     set score08.tu2008_dec(keep= permid CBSA: CensusBlockGroup: CensusSuffix: CensusTract: CountyFIPSCode: Latitude: Longitude: StateFIPSCode: zipcode);
run;

proc sql noprint;
     select trim(name) || '=' || 'tu07_' || name
     into :renamelist07 separated by ' '
     from dictionary.columns
     where libname='WORK' and memname='SCORE07' and (substr(name,1,4)='CBSA' 
     	   		      			    or substr(name,1,6)='Census' 
						    or substr(name,1,6)='County' 
						    or substr(name,1,3)='Lat' 
						    or substr(name,1,4)='Long' 
						    or substr(name,1,5)='State'); 
quit;

proc sql noprint;
     select trim(name) || '=' || 'tu08_' || trim(name)
     into :renamelist08 separated by ' '
     from dictionary.columns
     where libname='WORK' and memname='SCORE08' and (substr(name,1,4)='CBSA' 
     	   		      			    or substr(name,1,6)='Census'
						    or substr(name,1,6)='County' 
						    or substr(name,1,3)='Lat' 
						    or substr(name,1,4)='Long' 
						    or substr(name,1,5)='State'); 
quit;

proc sort data=score07; by permid; run;
proc sort data=score08; by permid; run;

data score0708;
     merge score07(in=in1 rename=(&renamelist07)) score08(in=in2 rename=(&renamelist08));
     by permid;
     bothyears=0;
     if in1 and in2 then bothyears=1;

     *this deals with obs for which we dont have data in both years;
     tu0708_CBSA97 = max(tu07_CBSA97,tu08_CBSA97);
     tu0708_CBSA99 = max(tu07_CBSA99,tu08_CBSA99);
     tu0708_CBSA01 = max(tu07_CBSA01,tu08_CBSA01);
     tu0708_CensusBlockGroup97 = max(tu07_CensusBlockGroup97,tu08_CensusBlockGroup97);
     tu0708_CensusBlockGroup99 = max(tu07_CensusBlockGroup99,tu08_CensusBlockGroup99);
     tu0708_CensusBlockGroup01 = max(tu07_CensusBlockGroup01,tu08_CensusBlockGroup01);
     tu0708_CensusSuffix97 = max(tu07_CensusSuffix97,tu08_CensusSuffix97);
     tu0708_CensusSuffix99 = max(tu07_CensusSuffix99,tu08_CensusSuffix99);
     tu0708_CensusSuffix01 = max(tu07_CensusSuffix01,tu08_CensusSuffix01);
     tu0708_CensusTract97 = max(tu07_CensusTract97,tu08_CensusTract97);
     tu0708_CensusTract99 = max(tu07_CensusTract99,tu08_CensusTract99);
     tu0708_CensusTract01 = max(tu07_CensusTract01,tu08_CensusTract01);
     tu0708_CountyFIPSCode97 = max(tu07_CountyFIPSCode97,tu08_CountyFIPSCode97);
     tu0708_CountyFIPSCode99 = max(tu07_CountyFIPSCode99,tu08_CountyFIPSCode99);
     tu0708_CountyFIPSCode01 = max(tu07_CountyFIPSCode01,tu08_CountyFIPSCode01);
     tu0708_Latitude97 = max(tu07_Latitude97,tu08_Latitude97);
     tu0708_Latitude99 = max(tu07_Latitude99,tu08_Latitude99);
     tu0708_Latitude01 = max(tu07_Latitude01,tu08_Latitude01);
     tu0708_Longitude97 = max(tu07_Longitude97,tu08_Longitude97);
     tu0708_Longitude99 = max(tu07_Longitude99,tu08_Longitude99);
     tu0708_Longitude01 = max(tu07_Longitude01,tu08_Longitude01);
     tu0708_StateFIPSCode97 = max(tu07_StateFIPSCode97,tu08_StateFIPSCode97);
     tu0708_StateFIPSCode99 = max(tu07_StateFIPSCode99,tu08_StateFIPSCode99);
     tu0708_StateFIPSCode01 = max(tu07_StateFIPSCode01,tu08_StateFIPSCode01);

     keep permid tu0708: bothyears zipcode;
run;

proc sort data=in.ids;
     by permid;
run;

data temp;
     merge in.ids(in=in1 keep=teditseq97 teditseq99 teditseq01 permid) score0708(in=in2);
     by permid;
     if in1 and in2;
run;

proc sort data=temp;
     by teditseq97;
run;

data in.roll_trades_loc97;
     merge temp(keep=teditseq97 tu0708_CBSA97 tu0708_CensusBlockGroup97 tu0708_CensusSuffix97 tu0708_CensusTract97 tu0708_CountyFIPSCode97 tu0708_StateFIPSCode97 tu0708_Latitude97 tu0708_Longitude97 zipcode in=in1) in.roll_trades97(in=in2);

     has0708=0;
     if in1 then has0708=1;
     by teditseq97;
     if in2;

     rename tu0708_CBSA97 = CBSA_0708;
     rename tu0708_CensusBlockGroup97 = CensusBlockGroup_0708;
     rename tu0708_CensusSuffix97 = CensusSuffix_0708;
     rename tu0708_CensusTract97 = CensusTract_0708;
     rename tu0708_CountyFIPSCode97 = CountyFIPSCode_0708;
     rename tu0708_StateFIPSCode97 = StateFIPSCode97_0708;
     rename tu0708_Latitude97 = Latitude_0708;
     rename tu0708_Longitude97 = Longitude_0708;
     rename zipcode = ZIP97_0708;
run;

proc sort data=temp;
     by teditseq99;
run;

data in.roll_trades_loc99;
     merge temp(keep=teditseq99 tu0708_CBSA99 tu0708_CensusBlockGroup99 tu0708_CensusSuffix99 tu0708_CensusTract99 tu0708_CountyFIPSCode99 tu0708_StateFIPSCode99 tu0708_Latitude99 tu0708_Longitude99 zipcode in=in1) in.roll_trades99(in=in2);
     by teditseq99;
     has0708=0;
     if in1 then has0708=1;
     if in2;

     rename tu0708_CBSA99 = CBSA_0708;
     rename tu0708_CensusBlockGroup99 = CensusBlockGroup_0708;
     rename tu0708_CensusSuffix99 = CensusSuffix_0708;
     rename tu0708_CensusTract99 = CensusTract_0708;
     rename tu0708_CountyFIPSCode99 = CountyFIPSCode_0708;
     rename tu0708_StateFIPSCode99 = StateFIPSCode99_0708;
     rename tu0708_Latitude99 = Latitude_0708;
     rename tu0708_Longitude99 = Longitude_0708;
     rename zipcode = ZIP99_0708;
run;

proc sort data=temp;
     by teditseq01;
run;

data in.roll_trades_loc01;
     merge temp(keep=teditseq01 tu0708_CBSA01 tu0708_CensusBlockGroup01 tu0708_CensusSuffix01 tu0708_CensusTract01 tu0708_CountyFIPSCode01 tu0708_StateFIPSCode01 tu0708_Latitude01 tu0708_Longitude01 zipcode in=in1) in.roll_trades01(in=in2);
     by teditseq01;
     has0708=0;
     if in1 then has0708=1;
     if in2;

     rename tu0708_CBSA01 = CBSA;
     rename tu0708_CensusBlockGroup01 = CensusBlockGroup_0708;
     rename tu0708_CensusSuffix01 = CensusSuffix_0708;
     rename tu0708_CensusTract01 = CensusTract_0708;
     rename tu0708_CountyFIPSCode01 = CountyFIPSCode_0708;
     rename tu0708_StateFIPSCode01 = StateFIPSCode01_0708;
     rename tu0708_Latitude01 = Latitude_0708;
     rename tu0708_Longitude01 = Longitude_0708;
     rename zipcode = ZIP01_0708;
run;
endsas;



